Patch Lifecycle (Compliance)

Tracks the events emitted by the Approval engine and the events detected by the Patch Status v2 monitor and aligns this to a Patch lifecycle model.

Methodology: Kimball Star Schema.

Fact Type: Accumulating Snapshot

Grain: A Patch Guid on a Device. There is only 1 row per Device/Patch Guid combo

The major phases of the lifecycle as:

  • No Approval: A patch has been detected by the Operating System as required and the Patch Server notified. The MSP has yet to take any approval actions.

  • Not Required: The MSP has made the positive decision that a patch should be Declined or Not Approved.

  • Pending: A patch that has been Approved but has not yet been Installed i.e no installation event has yet been detected.

  • Installed: A patch has been installed on a Device. Specifically this means that the first installed detection event has occurred.

  • Removed: A patch that was previously installed is no longer on the Device. Specifically this means that post installation a ‘Not Installed’ detection event has been received

Patch Lifecycle (Compliance) schema

Patch Lifecycle (Compliance) table joins

FACT: FACT_PATCH_LIFECYCLE

Dimension Dimension key Fact key Cardinality

DIM_TENANT

TENANT_DK

TENANT_DK

1:N

DIM_CUSTOMER_LATEST

CUSTOMER_DK

CUSTOMER_DK

1:N

DIM_SITE_LATEST

SITE_DK

SITE_DK

1:N

DIM_DEVICE_SUMMARY_LATEST

DEVICE_DK

DEVICE_DK

1:N

DIM_DATE_APPROVAL *

CALENDAR_DATE

APPROVAL_DATE_EARLIEST

1:N

DIM_DATE_INSTALLED *

CALENDAR_DATE

INSTALLED_EARLIEST

1:N

DIM_PATCH_ANDR

PATCH_DK

PATCH_DK

1:N

* DIM_DATE_APPROVAL and DIM_DATE_INSTALLED are example alias tables of DIM_DATE.

Boilerplate for Patch Lifecycle (Compliance) joins

Copy 
with DIM_DATE_APPROVAL as
(Select * from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_DATE_INSTALLED as 
(select * from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE)
Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_PATCH_LIFECYCLE f
Join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CUSTOMER_LATEST cl on f.Customer_DK = cl.Customer_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_SITE_LATEST sl on f.Site_DK = sl.Site_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DEVICE_SUMMARY_LATEST dl on f.Device_DK = dl.Device_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE_APPROVAL da on f.Approval_Date_Earliest = da.Calendar_Date
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE_INSTALLED di on f.Installed_Earliest = di.Calendar_Date
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_PATCH_ANDR p on f.Patch_DK = p.Patch_DK
-- Group By columns here
-- Having clauses here
-- Order by columns here